<?php
namespace app\manage\model;
use think\Db;

class Data extends Base
{
    /////////////////////游戏统计
    public function selectGameNow($plat='', $startTime, $endTime, $gameId='', $channelId='', $orderBy='pay_money', $gameIdStr=''){
        $year = date('Y', $startTime);
        $month = date('Ym', $startTime);

        //平台条件
        if($plat=='cps'){
            $whereType = " && channel_id='1000'";
        }else if($plat=='sdk'){
            $whereType = " && channel_id>='5000'";
        }else{
            $whereType = '';
        }

        //游戏条件
        $whereGameId = '';
        if($gameId){
            $whereGameId .= " && game_id={$gameId}";
        }

        //多终端条件
        $whereGameId .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道条件
        $whereChannelId1 = '';
        $whereChannelId2 = '';
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $whereChannelId1 .= " && channel_id=1000 && cps_id in({$allChannelId})";
                $whereChannelId2 .= " && channel_id=1000 && reg_cps_id in({$allChannelId})";
            }else{
                $whereChannelId1 .= " && channel_id={$channelId}";
                $whereChannelId2 .= " && channel_id={$channelId}";
            }
        }

        $whereCp = '';
        if($gameIdStr){
            $whereCp = " && game_id in ({$gameIdStr})";
        }

        //搜索条件
        $whereAdd = "bond_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 .$whereCp; //新增条件
        $whereRole = "create_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 .$whereCp; //创角条件
        $whereActive = "in_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 .$whereCp; //活跃条件
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereGameId . $whereType . $whereChannelId2 . $whereCp; //订单条件

        /*点西游特殊处理*/
        if(session('admin.id')==43 && session('admin.account')=='dianxiyou'){
            $whereAdd .= " && bond_time<1525104000";
            $whereRole .= " && create_time<1525104000";
            $whereActive .= " && in_time<1525104000";
            $whereOrder .= " && pay_time<1525104000";
        }

        //新增
        $add = Db::name('GameUserY_'.$year)
            ->field("game_id, count(*) AS game_add")
            ->where($whereAdd)
            ->group("game_id")
            ->select();

        //创角
        $role = Db::name('GameUserRoleY_'.$year)
            ->field("game_id, count(*) AS game_role")
            ->where($whereRole)
            ->group("game_id")
            ->select();

        //活跃
        $active = Db::name('GameUserLogM_'.$month)
            ->field("game_id, count(DISTINCT(my_user_id)) AS game_active")
            ->where($whereActive)
            ->group("game_id")
            ->select();

        $order = Db::name('PayOrderY_'.$year)
            ->field("game_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
            ->where($whereOrder)
            ->group("game_id")
            ->select();

        //统计总数组
        $data = [];
        foreach($add as $k=>$v){
            $data[$v['game_id']]['game_add'] = $v['game_add'];
        }
        foreach($role as $k=>$v){
            $data[$v['game_id']]['game_role'] = $v['game_role'];
        }
        foreach($active as $k=>$v){
            $data[$v['game_id']]['game_active'] = $v['game_active'];
        }
        foreach($order as $k=>$v){
            $data[$v['game_id']]['pay_number'] = $v['pay_number'];
            $data[$v['game_id']]['pay_money'] = $v['pay_money']/100;
        }

        $allData['count'] = count($data);
        $allData['data'] = [];
        $gameIdName = model('manage/Game')->gameIdName();
        foreach($data as $k=>$v) {
            $gameAdd = isset($v['game_add']) ? $v['game_add'] : 0;
            $gameRole = isset($v['game_role']) ? $v['game_role'] : 0;
            $gameActive = isset($v['game_active']) ? $v['game_active'] : 0;
            $payNumber = isset($v['pay_number']) ? $v['pay_number'] : 0;
            $payMoney = isset($v['pay_money']) ? $v['pay_money'] : 0;

            $allData['data'][] = [
                'daytime' => date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                'game_id' => $k,
                'game_name' => isset($gameIdName[ $k ]) ? $gameIdName[ $k ] : '',
                'game_add' => $gameAdd,
                'game_role' => $gameRole,
                'game_active' => $gameActive,//游戏活跃
                'pay_number' => $payNumber,//付费人数
                'pay_money' => round($payMoney, 2),//付费金额
                'pay_rate' => $gameActive ? round($payNumber/$gameActive*100, 2).'%' : '0%', //付费率
                'arpu' => $gameAdd ? round($payMoney/$gameAdd, 2) : 0,//ARPU
                'arppu' => $gameActive ? round($payMoney/$gameActive, 2) : 0,//ARPPU
                'arpppu' => $payNumber ? round($payMoney/$payNumber, 2) : 0
            ];
        }

        $allData['data'] = arraySequence($allData['data'], $orderBy);

        $sumAdd = Db::name('GameUserY_'.$year)->where($whereAdd)->count();
        $sumRole = Db::name('GameUserRoleY_'.$year)->where($whereRole)->count();
        $selectActive = Db::name('GameUserLogM_'.$month)->field("count(DISTINCT(my_user_id)) AS game_active")->where($whereActive)->group('game_id')->select();
        $sumActive = sumCount($selectActive, 'game_active');
        $selectNumber = Db::name('PayOrderY_'.$year)->field("count(DISTINCT(my_user_id)) AS pay_number")->where($whereOrder)->group('game_id')->select();
        $sumNumber = sumCount($selectNumber, 'pay_number');
        $totalPrice = Db::name('PayOrderY_'.$year)->where($whereOrder)->sum('total_price');
        $sumMoney = round($totalPrice/100, 2);

        $allData['sum']['game_add'] = $sumAdd;
        $allData['sum']['game_role'] = $sumRole;
        $allData['sum']['game_active'] = $sumActive;
        $allData['sum']['pay_number'] = $sumNumber;
        $allData['sum']['pay_money'] = $sumMoney;
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $sumAdd ? round($sumMoney/$sumAdd, 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($sumMoney/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($sumMoney/$allData['sum']['pay_number'], 2) : '0';
        return $allData;
    }

    public function selectGameOld($plat, $startTime, $endTime, $gameId='', $channelId='', $orderBy='pay_money', $gameIdStr=''){
        //数据条件
        $where = "daytime between '{$startTime}' AND '{$endTime}'";

        //平台条件
        if($plat=='cps'){
            $where .= ' && channel_id=1000';
        }else if($plat=='sdk'){
            $where .= ' && channel_id>=5000';
        }else{
            $where .= '';
        }

        //游戏条件
        $fieldTime = '';
        $groupTime = '';
        if($gameId){
            $where .= " AND game_id={$gameId}";
            $fieldTime = " ,FROM_UNIXTIME(daytime, '%Y-%m-%d') as date_time";
            $groupTime = ' ,date_time';
            $orderTime = 'date_time desc';
        }else{
            $orderTime = "{$orderBy} desc";
        }

        //多终端条件
        $where .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道条件
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && cps_id in ({$allChannelId})";
            }else{
                $where .= " && channel_id={$channelId}";
            }
        }

        if($gameIdStr){
            $where .= " && game_id in ({$gameIdStr})";
        }

        /*点西游特殊处理*/
        if(session('admin.id')==43 && session('admin.account')=='dianxiyou'){
            $where .= " && daytime<1525104000";
        }

        $count = Db::name('DataBuffer')
            ->field("*{$fieldTime}")
            ->where($where)
            ->group("game_id{$groupTime}")
            ->count();

        $data = Db::name('DataBuffer')
            ->field("daytime, game_id, game_name, sum(plat_add) as plat_add, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money{$fieldTime}")
            ->where($where)
            ->group("game_id{$groupTime}")
            ->order($orderTime)
            ->select();

        $sum = Db::name('DataBuffer')->field('sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money')->where($where)->find();

        $allData['count'] = $count;

        $allData['data'] = [];
        foreach($data as $v) {
            $allData['data'][] = [
                'daytime' => $gameId ? $v['date_time'] . ' - ' . $v['date_time'] : date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                'game_id' => $v['game_id'],
                'game_name' => $v['game_name'],
                'game_add' => $v['game_add'],
                'game_role' => $v['game_role'],
                'game_active' => $v['game_active'],
                'pay_number' => $v['pay_number'],
                'pay_money' => round($v['pay_money'],2),
                'pay_rate' => $v['game_active'] ? round($v['pay_number']/$v['game_active']*100, 2).'%' : '0%',
                'arpu' => $v['game_add'] ? round($v['pay_money']/$v['game_add'], 2) : 0,
                'arppu' => $v['game_active'] ? round($v['pay_money']/$v['game_active'], 2) : 0,
                'arpppu' =>$v['pay_number'] ? round($v['pay_money']/$v['pay_number'], 2) : 0
            ];
        }

        $allData['sum']['game_add'] = round($sum['game_add'], 2);
        $allData['sum']['game_role'] = round($sum['game_role'], 2);
        $allData['sum']['game_active'] = round($sum['game_active'], 2);
        $allData['sum']['pay_number'] = round($sum['pay_number'], 2);
        $allData['sum']['pay_money'] = round($sum['pay_money'], 2);
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $allData['sum']['game_add'] ? round($allData['sum']['pay_money']/$allData['sum']['game_add'], 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_money']/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($allData['sum']['pay_money']/$allData['sum']['pay_number'], 2) : '0';

        return $allData;
    }

    public function findGameNow($plat='', $startTime, $endTime, $gameId='', $time, $channelId='', $gameIdStr=''){
        $year = date('Y', $startTime);
        $month = date('Ym', $startTime);

        //平台条件
        if($plat=='cps'){
            $whereType = " && channel_id='1000'";
        }else if($plat=='sdk'){
            $whereType = " && channel_id>='5000'";
        }else{
            $whereType = '';
        }

        //游戏条件
        $whereGameId = '';
        if($gameId){
            $whereGameId .= " AND game_id={$gameId}";
        }

        //渠道条件
        $whereChannelId1 = '';
        $whereChannelId2 = '';
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $whereChannelId1 .= " && channel_id=1000 && cps_id in({$allChannelId})";
                $whereChannelId2 .= " && channel_id=1000 && reg_cps_id in({$allChannelId})";
            }else{
                $whereChannelId1 .= " && channel_id={$channelId}";
                $whereChannelId2 .= " && channel_id={$channelId}";
            }
        }

        $whereCp = '';
        if($gameIdStr){
            $whereCp = " && game_id in ({$gameIdStr})";
        }

        //搜索条件
        $whereAdd = "bond_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 . $whereCp; //新增条件
        $whereRole = "create_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 . $whereCp; //创角条件
        $whereActive = "in_time between {$startTime} AND {$endTime}" . $whereGameId . $whereType . $whereChannelId1 . $whereCp; //活跃条件
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereGameId . $whereType . $whereChannelId2 . $whereCp; //订单条件

        /*点西游特殊处理*/
        if(session('admin.id')==43 && session('admin.account')=='dianxiyou'){
            $whereAdd .= " && bond_time<1525104000";
            $whereRole .= " && create_time<1525104000";
            $whereActive .= " && in_time<1525104000";
            $whereOrder .= " && pay_time<1525104000";
        }


        //新增
        $add = Db::name('GameUserY_'.$year)
            ->field("FROM_UNIXTIME(bond_time,'%H') as date_time, count(*) AS game_add")
            ->where($whereAdd)
            ->group("date_time")
            ->select();

        //创角
        $role = Db::name('GameUserRoleY_'.$year)
            ->field("FROM_UNIXTIME(create_time,'%H') as date_time, count(*) AS game_role")
            ->where($whereRole)
            ->group("date_time")
            ->select();

        //活跃
        $active = Db::name('GameUserLogM_'.$month)
            ->field("FROM_UNIXTIME(in_time,'%H') AS date_time, count(DISTINCT(my_user_id)) AS game_active")
            ->where($whereActive)
            ->group("date_time")
            ->select();

        $order = Db::name('PayOrderY_'.$year)
            ->field("FROM_UNIXTIME(pay_time,'%H') AS date_time, sum(total_price)/100 AS pay_money")
            ->where($whereOrder)
            ->group("date_time")
            ->select();

        $allData[0]['name'] = '游戏新增';
        $allData[0]['data'] = chartHourZero($add, 'date_time', 'game_add', $time);

        $allData[1]['name'] = '游戏创角';
        $allData[1]['data'] = chartHourZero($role, 'date_time', 'game_role', $time);

        $allData[2]['name'] = '游戏活跃';
        $allData[2]['data'] = chartHourZero($active, 'date_time', 'game_active', $time);

        $allData[3]['name'] = '付费金额';
        $allData[3]['data'] = chartHourZero($order, 'date_time', 'pay_money', $time);

        return json_encode($allData);
    }

    public function findGameOld($plat='', $startTime, $endTime, $gameId='', $groupType='', $channelId='', $gameIdStr=''){
        $where = "daytime between '{$startTime}' AND '{$endTime}'";
        if($plat=='cps'){
            $where .= ' && channel_id=1000';
        }else if($plat=='sdk'){
            $where .= ' && channel_id>=5000';
        }else{
            $where .= '';
        }

        if($gameId){
            $where .= " AND game_id={$gameId}";
        }

        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && cps_id in({$allChannelId})";
            }else{
                $where .= " && channel_id={$channelId}";
            }
        }

        if($gameIdStr){
            $where .= " && game_id in ({$gameIdStr})";
        }

        /*点西游特殊处理*/
        if(session('admin.id')==43 && session('admin.account')=='dianxiyou'){
            $where .= " && daytime<1525104000";
        }

        $data = Db::name('DataBuffer')
            ->field("FROM_UNIXTIME(daytime,'{$groupType}') as date_time, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_money) as pay_money")
            ->where($where)
            ->group('game_id, date_time')
            ->select();

        $add = [];
        $role = [];
        $active = [];
        $order = [];
        foreach ($data as $k=>$v){
            $add[$k]['date_time'] = $v['date_time'];
            $add[$k]['game_add'] = $v['game_add'];

            $role[$k]['date_time'] = $v['date_time'];
            $role[$k]['game_role'] = $v['game_role'];

            $active[$k]['date_time'] = $v['date_time'];
            $active[$k]['game_active'] = $v['game_active'];

            $order[$k]['date_time'] = $v['date_time'];
            $order[$k]['pay_money'] = $v['pay_money'];
        }

        $allData[0]['name'] = '游戏新增';
        $allData[0]['data'] = chartDayZero($add, 'date_time', 'game_add', $startTime, $endTime, $groupType);

        $allData[1]['name'] = '游戏创角';
        $allData[1]['data'] = chartDayZero($role, 'date_time', 'game_role', $startTime, $endTime, $groupType);

        $allData[2]['name'] = '游戏活跃';
        $allData[2]['data'] = chartDayZero($active, 'date_time', 'game_active', $startTime, $endTime, $groupType);

        $allData[3]['name'] = '付费金额';
        $allData[3]['data'] = chartDayZero($order, 'date_time', 'pay_money', $startTime, $endTime, $groupType);

        return json_encode($allData);
    }

    //////////////////////渠道统计
    public function selectChannelNow($plat='', $startTime, $endTime, $cpsId='', $channelId='', $orderBy='pay_money'){
        $year = date('Y', $startTime);
        $month = date('Ym', $startTime);

        //平台条件
        if($plat=='cps'){
            $whereType = " && channel_id='1000'";
        }else if($plat=='sdk'){
            $whereType = " && channel_id>='5000'";
        }else{
            $whereType = '';
        }

        //多终端条件
        $whereType .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道条件
        $whereCpsId1 = '';
        $whereCpsId2 = '';
        if($cpsId){
            if($cpsId < 5000){
                //如果是渠道后台，则查询一个渠道，运营后台，查询所有渠道
                if($channelId){
                    $whereCpsId1 .= " && channel_id=1000 && cps_id={$cpsId}";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id={$cpsId}";
                }else{
                    $allCpsId = model('manage/channel')->allChannelId($cpsId);
                    $whereCpsId1 .= " && channel_id=1000 && cps_id in({$allCpsId})";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id in({$allCpsId})";
                }
            }else{
                $whereCpsId1 .= " && channel_id={$cpsId}";
                $whereCpsId2 .= " && channel_id={$cpsId}";
            }
        }

        //渠道后台默认条件
        $whereChannelId1 = '';
        $whereChannelId2 = '';
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $whereChannelId1 .= " && channel_id=1000 && cps_id in({$allChannelId})";
                $whereChannelId2 .= " && channel_id=1000 && reg_cps_id in({$allChannelId})";
            }else{
                $whereChannelId1 .= " && channel_id={$channelId}";
                $whereChannelId2 .= " && channel_id={$channelId}";
            }
        }

        //搜索条件
        $wherePlat = "reg_time between {$startTime} AND {$endTime}" . $whereCpsId2 . $whereType . $whereChannelId2;
        $whereAdd = "bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereRole = "create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereActive = "in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereType . $whereChannelId2;

        $wherePlatCps = "channel_id=1000 && reg_time between {$startTime} AND {$endTime}" . $whereCpsId2 . $whereType . $whereChannelId2;
        $whereAddCps = "channel_id=1000 && bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereRoleCps = "channel_id=1000 && create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereActiveCps = "channel_id=1000 && in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereOrderCps = "channel_id=1000 && pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereType . $whereChannelId2;

        $wherePlatSdk = "channel_id>=5000 && reg_time between {$startTime} AND {$endTime}" . $whereCpsId2 . $whereType . $whereChannelId2;
        $whereAddSdk = "channel_id>=5000 && bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereRoleSdk = "channel_id>=5000 && create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereActiveSdk = "channel_id>=5000 && in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereOrderSdk = "channel_id>=5000 && pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereType . $whereChannelId2;

        //先查总和
        $platCps = [];
        $addCps = [];
        $roleCps = [];
        $activeCps = [];
        $orderCps = [];
        $platSdk = [];
        $addSdk = [];
        $roleSdk = [];
        $activeSdk = [];
        $orderSdk = [];
        if($plat=='cps' || $plat==''){
            $platCps = Db::name('User')
                ->field("reg_cps_id as channel_id, count(*) AS plat_add")
                ->where($wherePlatCps)
                ->group("reg_cps_id")
                ->select();
            $addCps = Db::name('GameUserY_'.$year)
                ->field("cps_id as channel_id, count(*) AS game_add")
                ->where($whereAddCps)
                ->group("cps_id")
                ->select();
            $roleCps = Db::name('GameUserRoleY_'.$year)
                ->field("cps_id as channel_id, count(*) AS game_role")
                ->where($whereRoleCps)
                ->group("cps_id")
                ->select();
            $activeCps = Db::name('GameUserLogM_'.$month)
                ->field("cps_id as channel_id, count(DISTINCT(my_user_id)) AS game_active")
                ->where($whereActiveCps)
                ->group("cps_id")
                ->select();
            $orderCps = Db::name('PayOrderY_'.$year)
                ->field("reg_cps_id as channel_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
                ->where($whereOrderCps)
                ->group("reg_cps_id")
                ->select();
        }

        if($plat=='sdk' || $plat==''){
            $platSdk = Db::name('User')
                ->field("channel_id, count(*) AS plat_add")
                ->where($wherePlatSdk)
                ->group("channel_id")
                ->select();
            $addSdk = Db::name('GameUserY_'.$year)
                ->field("channel_id, count(*) AS game_add")
                ->where($whereAddSdk)
                ->group("channel_id")
                ->select();
            $roleSdk = Db::name('GameUserRoleY_'.$year)
                ->field("channel_id, count(*) AS game_role")
                ->where($whereRoleSdk)
                ->group("channel_id")
                ->select();
            $activeSdk = Db::name('GameUserLogM_'.$month)
                ->field("channel_id, count(DISTINCT(my_user_id)) AS game_active")
                ->where($whereActiveSdk)
                ->group("channel_id")
                ->select();
            $orderSdk = Db::name('PayOrderY_'.$year)
                ->field("channel_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
                ->where($whereOrderSdk)
                ->group("channel_id")
                ->select();
        }

        $plat = array_merge($platCps, $platSdk);
        $add = array_merge($addCps, $addSdk);
        $role = array_merge($roleCps, $roleSdk);
        $active = array_merge($activeCps, $activeSdk);
        $order = array_merge($orderCps, $orderSdk);

        //统计总数组
        $data = [];
        foreach($plat as $k=>$v){
            $data[$v['channel_id']]['plat_add'] = $v['plat_add'];
        }
        foreach($add as $k=>$v){
            $data[$v['channel_id']]['game_add'] = $v['game_add'];
        }
        foreach($role as $k=>$v){
            $data[$v['channel_id']]['game_role'] = $v['game_role'];
        }
        foreach($active as $k=>$v){
            $data[$v['channel_id']]['game_active'] = $v['game_active'];
        }
        foreach($order as $k=>$v){
            $data[$v['channel_id']]['pay_number'] = $v['pay_number'];
            $data[$v['channel_id']]['pay_money'] = $v['pay_money']/100;
        }

        $allData['count'] = count($data);
        $allData['data'] = [];
        $channelIdName = model('manage/Channel')->channelIdName();
        foreach($data as $k=>$v) {
            $platAdd = isset($v['plat_add']) ? $v['plat_add'] : 0;
            $gameAdd = isset($v['game_add']) ? $v['game_add'] : 0;
            $gameRole = isset($v['game_role']) ? $v['game_role'] : 0;
            $gameActive = isset($v['game_active']) ? $v['game_active'] : 0;
            $payNumber = isset($v['pay_number']) ? $v['pay_number'] : 0;
            $payMoney = isset($v['pay_money']) ? $v['pay_money'] : 0;

            $allData['data'][] = [
                'daytime' => date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                'channel_id' => $k,
                'channel_name' => isset($channelIdName[ $k ]) ? $channelIdName[ $k ] : '',
                'plat_add' => $platAdd,
                'game_add' => $gameAdd,
                'game_role' => $gameRole,
                'game_active' => $gameActive,//游戏活跃
                'pay_number' => $payNumber,//付费人数
                'pay_money' => round($payMoney, 2),//付费金额
                'pay_rate' => $gameActive ? round($payNumber/$gameActive*100, 2).'%' : '0%', //付费率
                'arpu' => $gameAdd ? round($payMoney/$gameAdd, 2) : 0,//ARPU
                'arppu' => $gameActive ? round($payMoney/$gameActive, 2) : 0,//ARPPU
                'arpppu' => $payNumber ? round($payMoney/$payNumber, 2) : 0
            ];
        }

        $allData['data'] = arraySequence($allData['data'], $orderBy);

        $sumPlat = Db::name('User')->where($wherePlat)->count();
        $sumAdd = Db::name('GameUserY_'.$year)->where($whereAdd)->count();
        $sumRole = Db::name('GameUserRoleY_'.$year)->where($whereRole)->count();

        $selectActive = Db::name('GameUserLogM_'.$month)->field("count(DISTINCT(my_user_id)) AS game_active")->where($whereActive)->group('game_id')->select();
        $sumActive = sumCount($selectActive, 'game_active');
        $selectNumber = Db::name('PayOrderY_'.$year)->field("count(DISTINCT(my_user_id)) AS pay_number")->where($whereOrder)->group('game_id')->select();
        $sumNumber = sumCount($selectNumber, 'pay_number');

        $totalPrice = Db::name('PayOrderY_'.$year)->where($whereOrder)->sum('total_price');
        $sumMoney = round($totalPrice/100, 2);

        $allData['sum']['plat_add'] = $sumPlat;
        $allData['sum']['game_add'] = $sumAdd;
        $allData['sum']['game_role'] = $sumRole;
        $allData['sum']['game_active'] = $sumActive;
        $allData['sum']['pay_number'] = $sumNumber;
        $allData['sum']['pay_money'] = $sumMoney;
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $sumAdd ? round($sumMoney/$sumAdd, 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($sumMoney/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($sumMoney/$allData['sum']['pay_number'], 2) : '0';
        return $allData;
    }

    public function selectChannelOld($plat, $startTime, $endTime, $cpsId='', $channelId='', $orderBy='pay_money'){
        //数据条件
        $where = "daytime between '{$startTime}' AND '{$endTime}'";

        //平台条件
        if($plat=='cps'){
            $where .= ' && channel_id=1000';
        }else if($plat=='sdk'){
            $where .= ' && channel_id>=5000';
        }else{
            $where .= '';
        }

        //游戏条件
        $fieldTime = '';
        $groupTime = '';
        if($cpsId){
            if($cpsId < 5000){
                if($channelId){
                    $where .= " && channel_id=1000 && cps_id={$cpsId}";
                }else{
                    $allCpsId = model('manage/channel')->allChannelId($cpsId);
                    $where .= " && channel_id=1000 && cps_id in({$allCpsId})";
                }
            }else{
                $where .= " && channel_id={$cpsId}";
            }
            $fieldTime = " ,FROM_UNIXTIME(daytime, '%Y-%m-%d') as date_time";
            $groupTime = ' ,date_time';
        }

        //多终端条件
        $where .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道后台默认条件
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && cps_id in({$allChannelId})";
            }else{
                $where .= " && channel_id={$channelId}";
            }
            $fieldTime = " ,FROM_UNIXTIME(daytime, '%Y-%m-%d') as date_time";
            $groupTime = ' ,date_time';
        }

        $dataCps = [];
        $dataSdk = [];
        if($plat=='cps' || $plat==''){
            $whereCps = $where . ' && channel_id=1000';
            $dataCps = Db::name('DataBuffer')
                ->field("daytime, cps_id as channel_id, cps_name, sum(plat_add) as plat_add, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money{$fieldTime}")
                ->where($whereCps)
                ->group("cps_id{$groupTime}")
                ->select();
        }

        if($plat=='sdk' || $plat==''){
            $whereSdk = $where . ' && channel_id>=5000';
            $dataSdk = Db::name('DataBuffer')
                ->field("daytime, channel_id, cps_name, sum(plat_add) as plat_add, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money{$fieldTime}")
                ->where($whereSdk)
                ->group("channel_id{$groupTime}")
                ->select();
        }

        $mergeData = array_merge($dataCps, $dataSdk);

        if($cpsId){
            $data = arraySequence($mergeData, 'daytime');
        }else{
            $data = arraySequence($mergeData, $orderBy);
        }

        $sum = Db::name('DataBuffer')
            ->field('sum(plat_add) as plat_add, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money')
            ->where($where)
            ->find();

        $allData['count'] = count($data);
        $allData['data'] = [];
        foreach($data as $v) {
            $allData['data'][] = [
                'daytime' => $cpsId ? $v['date_time'] . ' - ' . $v['date_time'] : date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                'channel_id' => $v['channel_id'],
                'channel_name' => $v['cps_name'],
                'plat_add' => $v['plat_add'],
                'game_add' => $v['game_add'],
                'game_role' => $v['game_role'],
                'game_active' => $v['game_active'],
                'pay_number' => $v['pay_number'],
                'pay_money' => round($v['pay_money'],2),
                'pay_rate' => $v['game_active'] ? round($v['pay_number']/$v['game_active']*100, 2).'%' : '0%',
                'arpu' => $v['game_add'] ? round($v['pay_money']/$v['game_add'], 2) : 0,
                'arppu' => $v['game_active'] ? round($v['pay_money']/$v['game_active'], 2) : 0,
                'arpppu' =>$v['pay_number'] ? round($v['pay_money']/$v['pay_number'], 2) : 0
            ];
        }

        $allData['sum']['plat_add'] = round($sum['plat_add'], 2);
        $allData['sum']['game_add'] = round($sum['game_add'], 2);
        $allData['sum']['game_role'] = round($sum['game_role'], 2);
        $allData['sum']['game_active'] = round($sum['game_active'], 2);
        $allData['sum']['pay_number'] = round($sum['pay_number'], 2);
        $allData['sum']['pay_money'] = round($sum['pay_money'], 2);
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $allData['sum']['game_add'] ? round($allData['sum']['pay_money']/$allData['sum']['game_add'], 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_money']/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($allData['sum']['pay_money']/$allData['sum']['pay_number'], 2) : '0';

        return $allData;
    }

    public function findChannelNow($plat='', $startTime, $endTime, $cpsId='', $time, $channelId=''){
        $year = date('Y', $startTime);
        $month = date('Ym', $startTime);

        //平台条件
        if($plat=='cps'){
            $whereType = " && channel_id='1000'";
        }else if($plat=='sdk'){
            $whereType = " && channel_id>='5000'";
        }else{
            $whereType = '';
        }

        //渠道条件
        $whereCpsId1 = '';
        $whereCpsId2 = '';
        if($cpsId){
            if($cpsId < 5000){
                //如果是渠道后台，则查询一个渠道，运营后台，查询所有渠道
                if($channelId){
                    $whereCpsId1 .= " && channel_id=1000 && cps_id={$cpsId}";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id={$cpsId}";
                }else{
                    $allCpsId = model('manage/channel')->allChannelId($cpsId);
                    $whereCpsId1 .= " && channel_id=1000 && cps_id in({$allCpsId})";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id in({$allCpsId})";
                }
            }else{
                $whereCpsId1 .= " && channel_id={$cpsId}";
                $whereCpsId2 .= " && channel_id={$cpsId}";
            }
        }

        //渠道后台默认条件
        $whereChannelId1 = '';
        $whereChannelId2 = '';
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $whereChannelId1 .= " && channel_id=1000 && cps_id in({$allChannelId})";
                $whereChannelId2 .= " && channel_id=1000 && reg_cps_id in({$allChannelId})";
            }else{
                $whereChannelId1 .= " && channel_id={$channelId}";
                $whereChannelId2 .= " && channel_id={$channelId}";
            }
        }

        //搜索条件
        $whereAdd = "bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereRole = "create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereActive = "in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereType . $whereChannelId1;
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereType . $whereChannelId2;

        //新增
        $add = Db::name('GameUserY_'.$year)
            ->field("FROM_UNIXTIME(bond_time,'%H') as date_time, count(*) AS game_add")
            ->where($whereAdd)
            ->group("date_time")
            ->select();

        //创角
        $role = Db::name('GameUserRoleY_'.$year)
            ->field("FROM_UNIXTIME(create_time,'%H') as date_time, count(*) AS game_role")
            ->where($whereRole)
            ->group("date_time")
            ->select();

        //活跃
        $active = Db::name('GameUserLogM_'.$month)
            ->field("FROM_UNIXTIME(in_time,'%H') AS date_time, count(DISTINCT(my_user_id)) AS game_active")
            ->where($whereActive)
            ->group("date_time")
            ->select();

        $order = Db::name('PayOrderY_'.$year)
            ->field("FROM_UNIXTIME(pay_time,'%H') AS date_time, sum(total_price)/100 AS pay_money")
            ->where($whereOrder)
            ->group("date_time")
            ->select();

        $allData[0]['name'] = '游戏新增';
        $allData[0]['data'] = chartHourZero($add, 'date_time', 'game_add', $time);

        $allData[1]['name'] = '游戏创角';
        $allData[1]['data'] = chartHourZero($role, 'date_time', 'game_role', $time);

        $allData[2]['name'] = '游戏活跃';
        $allData[2]['data'] = chartHourZero($active, 'date_time', 'game_active', $time);

        $allData[3]['name'] = '付费金额';
        $allData[3]['data'] = chartHourZero($order, 'date_time', 'pay_money', $time);

        return json_encode($allData);
    }

    public function findChannelOld($plat='', $startTime, $endTime, $cpsId='', $groupType='', $channelId=''){
        $where = "daytime between '{$startTime}' AND '{$endTime}'";
        if($plat=='cps'){
            $where .= ' && channel_id=1000';
        }else if($plat=='sdk'){
            $where .= ' && channel_id>=5000';
        }else{
            $where .= '';
        }

        if($cpsId){
            if($cpsId < 5000){
                $allCpsId = model('manage/channel')->allChannelId($cpsId);
                $where .= " && channel_id=1000 && cps_id in({$allCpsId})";
            }else{
                $where .= " && channel_id={$cpsId}";
            }
        }

        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && cps_id in({$allChannelId})";
            }else{
                $where .= " && channel_id={$channelId}";
            }
        }

        $data = Db::name('DataBuffer')
            ->field("FROM_UNIXTIME(daytime,'{$groupType}') as date_time, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_money) as pay_money")
            ->where($where)
            ->group('date_time')
            ->select();

        $add = [];
        $role = [];
        $active = [];
        $order = [];
        foreach ($data as $k=>$v){
            $add[$k]['date_time'] = $v['date_time'];
            $add[$k]['game_add'] = $v['game_add'];

            $role[$k]['date_time'] = $v['date_time'];
            $role[$k]['game_role'] = $v['game_role'];

            $active[$k]['date_time'] = $v['date_time'];
            $active[$k]['game_active'] = $v['game_active'];

            $order[$k]['date_time'] = $v['date_time'];
            $order[$k]['pay_money'] = $v['pay_money'];
        }

        $allData[0]['name'] = '游戏新增';
        $allData[0]['data'] = chartDayZero($add, 'date_time', 'game_add', $startTime, $endTime, $groupType);

        $allData[1]['name'] = '游戏创角';
        $allData[1]['data'] = chartDayZero($role, 'date_time', 'game_role', $startTime, $endTime, $groupType);

        $allData[2]['name'] = '游戏活跃';
        $allData[2]['data'] = chartDayZero($active, 'date_time', 'game_active', $startTime, $endTime, $groupType);

        $allData[3]['name'] = '付费金额';
        $allData[3]['data'] = chartDayZero($order, 'date_time', 'pay_money', $startTime, $endTime, $groupType);

        return json_encode($allData);
    }

    //////////////////////渠道游戏统计
    public function selectChannelGameNow($page, $limit, $plat='', $startTime, $endTime, $cpsId='', $gameId='', $channelId=''){
        $year = date('Y', $startTime);
        $month = date('Ym', $startTime);

        //平台条件
        if($plat=='cps'){
            $whereType = " && channel_id='1000'";
        }else if($plat=='sdk'){
            $whereType = " && channel_id>='5000'";
        }else{
            $whereType = '';
        }

        //多终端条件
        $whereType .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道条件
        $whereCpsId1 = '';
        $whereCpsId2 = '';
        if($cpsId){
            if($cpsId < 5000){
                //如果是渠道后台，则查询一个渠道，运营后台，查询所有渠道
                if($channelId){
                    $whereCpsId1 .= " && channel_id=1000 && cps_id={$cpsId}";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id={$cpsId}";
                }else{
                    $allCpsId = model('manage/channel')->allChannelId($cpsId);
                    $whereCpsId1 .= " && channel_id=1000 && cps_id in({$allCpsId})";
                    $whereCpsId2 .= " && channel_id=1000 && reg_cps_id in({$allCpsId})";
                }
            }else{
                $whereCpsId1 .= " && channel_id={$cpsId}";
                $whereCpsId2 .= " && channel_id={$cpsId}";
            }
        }

        //游戏条件
        $whereGameId = '';
        if($gameId){
            $whereGameId .= " && game_id={$gameId}";
        }

        //渠道后台默认条件
        $whereChannelId1 = '';
        $whereChannelId2 = '';
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $whereChannelId1 .= " && channel_id=1000 && cps_id in({$allChannelId})";
                $whereChannelId2 .= " && channel_id=1000 && reg_cps_id in({$allChannelId})";
            }else{
                $whereChannelId1 .= " && channel_id={$channelId}";
                $whereChannelId2 .= " && channel_id={$channelId}";
            }
        }

        //搜索条件
        $whereAdd = "bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereRole = "create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereActive = "in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereOrder = "pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereGameId . $whereType . $whereChannelId2;

        $whereAddCps = "channel_id=1000 && bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereRoleCps = "channel_id=1000 && create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereActiveCps = "channel_id=1000 && in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereOrderCps = "channel_id=1000 && pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereGameId . $whereType . $whereChannelId2;

        $whereAddSdk = "channel_id>=5000 && bond_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereRoleSdk = "channel_id>=5000 && create_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereActiveSdk = "channel_id>=5000 && in_time between {$startTime} AND {$endTime}" . $whereCpsId1 . $whereGameId . $whereType . $whereChannelId1;
        $whereOrderSdk = "channel_id>=5000 && pay_time between {$startTime} AND {$endTime} AND debug=0 AND pay_result=1". $whereCpsId2 . $whereGameId . $whereType . $whereChannelId2;

        //先查总和
        $addCps = [];
        $roleCps = [];
        $activeCps = [];
        $orderCps = [];
        $addSdk = [];
        $roleSdk = [];
        $activeSdk = [];
        $orderSdk = [];
        if($plat=='cps' || $plat==''){
            $addCps = Db::name('GameUserY_'.$year)
                ->field("cps_id as channel_id, game_id, count(*) AS game_add")
                ->where($whereAddCps)
                ->group("cps_id, game_id")
                ->select();
            $roleCps = Db::name('GameUserRoleY_'.$year)
                ->field("cps_id as channel_id, game_id, count(*) AS game_role")
                ->where($whereRoleCps)
                ->group("cps_id, game_id")
                ->select();
            $activeCps = Db::name('GameUserLogM_'.$month)
                ->field("cps_id as channel_id, game_id, count(DISTINCT(my_user_id)) AS game_active")
                ->where($whereActiveCps)
                ->group("cps_id, game_id")
                ->select();
            $orderCps = Db::name('PayOrderY_'.$year)
                ->field("reg_cps_id as channel_id, game_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
                ->where($whereOrderCps)
                ->group("reg_cps_id, game_id")
                ->select();
        }

        if($plat=='sdk' || $plat==''){
            $addSdk = Db::name('GameUserY_'.$year)
                ->field("channel_id, game_id, count(*) AS game_add")
                ->where($whereAddSdk)
                ->group("channel_id, game_id")
                ->select();
            $roleSdk = Db::name('GameUserRoleY_'.$year)
                ->field("channel_id, game_id, count(*) AS game_role")
                ->where($whereRoleSdk)
                ->group("channel_id, game_id")
                ->select();
            $activeSdk = Db::name('GameUserLogM_'.$month)
                ->field("channel_id, game_id, count(DISTINCT(my_user_id)) AS game_active")
                ->where($whereActiveSdk)
                ->group("channel_id, game_id")
                ->select();
            $orderSdk = Db::name('PayOrderY_'.$year)
                ->field("channel_id, game_id, count(DISTINCT(my_user_id)) AS pay_number,sum(total_price) AS pay_money")
                ->where($whereOrderSdk)
                ->group("channel_id, game_id")
                ->select();
        }

        $add = array_merge($addCps, $addSdk);
        $role = array_merge($roleCps, $roleSdk);
        $active = array_merge($activeCps, $activeSdk);
        $order = array_merge($orderCps, $orderSdk);

        //统计总数组
        $data = [];
        foreach($add as $k=>$v){
            $data[$v['channel_id']][$v['game_id']]['game_add'] = $v['game_add'];
        }

        foreach($role as $k=>$v){
            $data[$v['channel_id']][$v['game_id']]['game_role'] = $v['game_role'];
        }
        foreach($active as $k=>$v){
            $data[$v['channel_id']][$v['game_id']]['game_active'] = $v['game_active'];
        }
        foreach($order as $k=>$v){
            $data[$v['channel_id']][$v['game_id']]['pay_number'] = $v['pay_number'];
            $data[$v['channel_id']][$v['game_id']]['pay_money'] = $v['pay_money']/100;
        }

        $towData = [];
        $channelIdName = model('manage/Channel')->channelIdName();
        $gameIdName = model('manage/Game')->gameIdName();
        foreach($data as $c=>$v1) {
            foreach ($v1 as $g=>$v){
                $gameAdd = isset($v['game_add']) ? $v['game_add'] : 0;
                $gameRole = isset($v['game_role']) ? $v['game_role'] : 0;
                $gameActive = isset($v['game_active']) ? $v['game_active'] : 0;
                $payNumber = isset($v['pay_number']) ? $v['pay_number'] : 0;
                $payMoney = isset($v['pay_money']) ? $v['pay_money'] : 0;

                $towData[] = [
                    'daytime' => date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                    'channel_id' => $c,
                    'channel_name' => isset($channelIdName[ $c ]) ? $channelIdName[ $c ] : '',
                    'game_id' => $g,
                    'game_name' => isset($gameIdName[ $g ]) ? $gameIdName[ $g ] : '',
                    'game_add' => $gameAdd,
                    'game_role' => $gameRole,
                    'game_active' => $gameActive,//游戏活跃
                    'pay_number' => $payNumber,//付费人数
                    'pay_money' => round($payMoney, 2),//付费金额
                    'pay_rate' => $gameActive ? round($payNumber/$gameActive*100, 2).'%' : '0%', //付费率
                    'arpu' => $gameAdd ? round($payMoney/$gameAdd, 2) : 0,//ARPU
                    'arppu' => $gameActive ? round($payMoney/$gameActive, 2) : 0,//ARPPU
                    'arpppu' => $payNumber ? round($payMoney/$payNumber, 2) : 0
                ];
            }
        }
        $allData['count'] = count($towData);
        $allData['data'] = array_slice($towData, ($page-1)*$limit, $limit);
        $allData['data'] = arraySequence($allData['data'], 'pay_money');

        $sumAdd = Db::name('GameUserY_'.$year)->where($whereAdd)->count();
        $sumRole = Db::name('GameUserRoleY_'.$year)->where($whereRole)->count();

        $selectActive = Db::name('GameUserLogM_'.$month)->field("count(DISTINCT(my_user_id)) AS game_active")->where($whereActive)->group('game_id')->select();
        $sumActive = sumCount($selectActive, 'game_active');
        $selectNumber = Db::name('PayOrderY_'.$year)->field("count(DISTINCT(my_user_id)) AS pay_number")->where($whereOrder)->group('game_id')->select();
        $sumNumber = sumCount($selectNumber, 'pay_number');

        $totalPrice = Db::name('PayOrderY_'.$year)->where($whereOrder)->sum('total_price');
        $sumMoney = round($totalPrice/100, 2);

        $allData['sum']['game_add'] = $sumAdd;
        $allData['sum']['game_role'] = $sumRole;
        $allData['sum']['game_active'] = $sumActive;
        $allData['sum']['pay_number'] = $sumNumber;
        $allData['sum']['pay_money'] = $sumMoney;
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $sumAdd ? round($sumMoney/$sumAdd, 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($sumMoney/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($sumMoney/$allData['sum']['pay_number'], 2) : '0';
        return $allData;
    }

    public function selectChannelGameOld($page, $limit, $plat, $startTime, $endTime, $cpsId='', $gameId='', $channelId=''){
        //数据条件
        $where = "daytime between '{$startTime}' AND '{$endTime}'";

        //平台条件
        if($plat=='cps'){
            $where .= ' && channel_id=1000';
        }else if($plat=='sdk'){
            $where .= ' && channel_id>=5000';
        }else{
            $where .= '';
        }

        //多终端条件
        $where .= model('manage/Game')->gameClient(input('game_server'), input('game_publish'), input('game_platform'), input('game_client'));

        //渠道条件
        if($cpsId){
            if($cpsId < 5000){
                if($channelId){
                    $where .= " && channel_id=1000 && cps_id={$cpsId}";
                }else{
                    $allCpsId = model('manage/channel')->allChannelId($cpsId);
                    $where .= " && channel_id=1000 && cps_id in({$allCpsId})";
                }
            }else{
                $where .= " && channel_id={$cpsId}";
            }
        }

        //游戏条件
        if($gameId){
            $where .= " && game_id={$gameId}";
        }

        $fieldTime = '';
        $groupTime = '';
        if($cpsId && $gameId){
            $fieldTime = " ,FROM_UNIXTIME(daytime, '%Y-%m-%d') as date_time";
            $groupTime = ' ,date_time';
        }

        //渠道后台默认条件
        if($channelId){
            if($channelId < 5000){
                $allChannelId = model('manage/channel')->allChannelId($channelId);
                $where .= " && channel_id=1000 && cps_id in({$allChannelId})";
            }else{
                $where .= " && channel_id={$channelId}";
            }
        }

        $dataCps = [];
        $dataSdk = [];
        if($plat=='cps' || $plat==''){
            $whereCps = $where . ' && channel_id=1000';
            $dataCps = Db::name('DataBuffer')
                ->field("daytime, cps_id as channel_id, cps_name, game_id, game_name, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money{$fieldTime}")
                ->where($whereCps)
                ->group("cps_id,game_id{$groupTime}")
                ->select();
        }

        if($plat=='sdk' || $plat==''){
            $whereSdk = $where . ' && channel_id>=5000';
            $dataSdk = Db::name('DataBuffer')
                ->field("daytime, channel_id, cps_name, game_id, game_name, sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money{$fieldTime}")
                ->where($whereSdk)
                ->group("channel_id,game_id{$groupTime}")
                ->select();
        }

        $mergeData = array_merge($dataCps, $dataSdk);

        if($cpsId&&$gameId){
            $data = arraySequence($mergeData, 'daytime');
        }else{
            $data = arraySequence($mergeData, 'pay_money');
        }

        $sum = Db::name('DataBuffer')
            ->field('sum(game_add) as game_add, sum(game_role) as game_role, sum(game_active) as game_active, sum(pay_number) as pay_number, sum(pay_money) as pay_money')
            ->where($where)
            ->find();

        $allData['count'] = count($data);

        $data = array_slice($data, ($page-1)*$limit, $limit);

        $allData['data'] = [];
        foreach($data as $v) {
            $allData['data'][] = [
                'daytime' => $cpsId&&$gameId ? $v['date_time'] . ' - ' . $v['date_time'] : date('Y-m-d', $startTime) . ' - ' . date('Y-m-d', $endTime),
                'channel_id' => $v['channel_id'],
                'channel_name' => $v['cps_name'],
                'game_id' => $v['game_id'],
                'game_name' => $v['game_name'],
                'game_add' => $v['game_add'],
                'game_role' => $v['game_role'],
                'game_active' => $v['game_active'],
                'pay_number' => $v['pay_number'],
                'pay_money' => round($v['pay_money'],2),
                'pay_rate' => $v['game_active'] ? round($v['pay_number']/$v['game_active']*100, 2).'%' : '0%',
                'arpu' => $v['game_add'] ? round($v['pay_money']/$v['game_add'], 2) : 0,
                'arppu' => $v['game_active'] ? round($v['pay_money']/$v['game_active'], 2) : 0,
                'arpppu' =>$v['pay_number'] ? round($v['pay_money']/$v['pay_number'], 2) : 0
            ];
        }


        $allData['sum']['game_add'] = round($sum['game_add'], 2);
        $allData['sum']['game_role'] = round($sum['game_role'], 2);
        $allData['sum']['game_active'] = round($sum['game_active'], 2);
        $allData['sum']['pay_number'] = round($sum['pay_number'], 2);
        $allData['sum']['pay_money'] = round($sum['pay_money'], 2);
        $allData['sum']['pay_rate'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_number']/$allData['sum']['game_active']*100, 2).'%' : '0%';
        $allData['sum']['arpu'] = $allData['sum']['game_add'] ? round($allData['sum']['pay_money']/$allData['sum']['game_add'], 2) : '0';
        $allData['sum']['arppu'] = $allData['sum']['game_active'] ? round($allData['sum']['pay_money']/$allData['sum']['game_active'], 2) : '0';
        $allData['sum']['arpppu'] = $allData['sum']['pay_number'] ? round($allData['sum']['pay_money']/$allData['sum']['pay_number'], 2) : '0';

        return $allData;
    }
}